<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/10/18
 * Time: 16:22
 */
class Sql {
    public $whereStr;
    protected $_conn;
    protected $_sqlTable;
    protected $_dbConfig;

    public function connect($config, $table) {

        try{

            $this->_sqlTable = strtolower($table);
            $this->_dbConfig = $config;

            $serviceName =$this->_dbConfig['serviceName'];
            $username = $this->_dbConfig['username'];
            $password = $this->_dbConfig['password'];
            $dbName = $this->_dbConfig['dbName'];
//                链接数据库
            $this->_conn = new mysqli($serviceName, $username, $password, $dbName);
        } catch (Exception $exception) {
            exit('错误: ' . $exception->getMessage());
        }
    }



//    像数据库添加数据
    public function add($param) {

        $sql = "insert into " . $this->_dbConfig['prefix'] . '_' . $this->_sqlTable . "" . $this->formatInsert($param) . ";";
        $this->query($sql);
    }

//    用于数据库where语句
    public function where($param) {
        $this->whereStr = "where " . $this->formatSqlParam($param, 'and');
        return $this;
    }

//    删除数据库数据
    public function delete() {
        $sql = "delete from " . $this->_dbConfig['prefix'] . "_" . $this->_sqlTable . " " . $this->whereStr . ";";
        $this->query($sql);
        return $this;
    }

//    更新数据库
      public function update($param) {

        $sql = "update " . $this->_dbConfig['prefix'] . "_" . $this->_sqlTable . " set " . $this->formatSqlParam($param, ',') ." " . $this->whereStr;
        $this->prequery($sql);
      }

    public function select() {
        $sql = "select * from " . $this->_dbConfig['prefix'] ."_" . $this->_sqlTable . " " . $this->whereStr.";";

        $result =$this->query($sql);

        return  $this->mysqlResultToArr($result);

    }

    //    获取所有数据
    public function selectAll() {
        $sql = 'select * from ' .$this->_dbConfig['prefix'] . '_' . $this->_sqlTable . ";";

        $result = $this->query($sql);

        return $this->mysqlResultToArr($result);
    }

    //    执行添加数据的sql语句,不可以被继承

    protected function prequery($sql) {

        $stmt = $this->_conn->prepare($sql);

        $result = $stmt->execute();

        $this->_conn->close();
        return $result;
    }

//    执行sql语句
    public function query($sql) {

        $result = $this->_conn->query($sql);
        $this->_conn->close();

        return $result;
    }

//    将['name'=>'xu'] 转化成 name='xu' 用于sql语句
    public function formatSqlParam($arr, $mark) {
        $keyArr = array();
        $valueArr = array();
        $result="";
        foreach ($arr as $key=>$value) {
            $result .=$mark . " ";
            $result .= sprintf("%s='%s'", $key, $value);
        }
        $result = trim($result, $mark);

        return $result;
    }
//    将传递的数组转换成用于 insert 的sql的字符串
    public function formatInsert($arr) {

        $keyArr = array();
        $valueArr = array();

        foreach ($arr as $key=>$value) {
            $keyArr[] = sprintf("%s", $key);
            $valueArr[] = sprintf("'%s'", $value);
        }

        $keyStr = implode(',' , $keyArr);
        $valueStr = implode(',' , $valueArr);

        return  sprintf("(%s) values (%s)", $keyStr, $valueStr);
    }

//    将查询数据库的数据转化成数组
    public function mysqlResultToArr($result) {
        $resultArr = array();
        if ($result) {
            while ($row = $result->fetch_assoc()) {
                array_push($resultArr, $row);
            }
        }
        return $resultArr;
    }
}